package DAO;

import connect.ConnectDB;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Set;
import java.util.TreeMap;
import java.util.Vector;
import javax.swing.JComboBox;
import javax.swing.table.DefaultTableModel;

public class VacancyDAO {

    Connection conn;
    ResultSet rsVac = null;
    Statement st;
    PreparedStatement pst;
    ConnectDB cdb;
    TreeMap<Integer, String> tmDep;
    TreeMap<String, String> tmDes;
    TreeMap<String, String> tmSec;
    TreeMap<String, String> tmStatus;

    public VacancyDAO() {
        cdb = new ConnectDB();
        cdb.getConnect();
        this.conn = cdb.conn;

        loadDataVac();
    }

    public void loadDataVac() {
        try {
            String query = "SELECT "
                    + "             * "
                    + "     FROM "
                    + "             Vacancies";
            st = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            rsVac = st.executeQuery(query);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String checkVacID(String VacID) {
        try {
            loadDataVac();
            while (rsVac.next()) {
                if (rsVac.getString("VACANCYID").equals(VacID)) {
                    return "Exist";
                }
            }
            return "Not exist";
        } catch (Exception e) {
            System.out.println("Error in checkVacId: " + e.getMessage());
            return "Error";
        }
    }

    public Vector loadVacToUpdate(String VacId) {
        Vector vVacData = new Vector();
        try {
            rsVac.beforeFirst();
            while (rsVac.next()) {
                if (VacId.equals(rsVac.getString("VACANCYID"))) {
                    
                    vVacData.add(rsVac.getString(1));   // Vac ID
                    
                    if (tmDep.containsKey(rsVac.getInt("DEPTNO"))) {    // Dep No
                        String depNo = rsVac.getInt("DEPTNO") + " - " + tmDep.get(rsVac.getInt("DEPTNO"));
                        vVacData.add(depNo);
                    }
                    
                    if (tmSec.containsKey(rsVac.getString("SECTID"))) { // Sec Id
                        String secId = rsVac.getString("SECTID") + " - " + tmSec.get(rsVac.getString("SECTID"));
                        vVacData.add(secId);
                    }
                    
                    if (tmDes.containsKey(rsVac.getString("DESIGNID"))) {   // Des Id
                        String desId = rsVac.getString("DESIGNID") + " - " + tmDes.get(rsVac.getString("DESIGNID"));
                        vVacData.add(desId);
                    }
                    
                    vVacData.add(rsVac.getString(5));       // No Of Vacancies
                    
                    if (tmStatus.containsKey(rsVac.getString("STATUS"))) {  // Status
                        String status = rsVac.getString("STATUS") + " - " + tmStatus.get(rsVac.getString("STATUS"));
                        vVacData.add(status);
                    }
                    
                    vVacData.add(rsVac.getString(7));   // Date
                    vVacData.add(rsVac.getString(8));   // Priority
                }
            }
            return vVacData;
        } catch (Exception e) {
            System.out.println("Error in loadVacToUpdateForm: " + e.getMessage());
            return null;
        }
    }

    public boolean insertVac(Vector vVacData) {
        try {
            String id = vVacData.get(0).toString();
            int depNo = getDepNo(vVacData);
            String desId = getDesId(vVacData);
            String secId = getSecId(vVacData);
            int NoOf = Integer.parseInt(vVacData.get(4).toString());
            String status = getStatus(vVacData);
            String date = vVacData.get(6).toString();
            String priority = vVacData.get(7).toString();

            rsVac.moveToInsertRow();

            rsVac.updateString(1, id);
            rsVac.updateInt(2, depNo);
            rsVac.updateString(3, secId);
            rsVac.updateString(4, desId);
            rsVac.updateInt(5, NoOf);
            rsVac.updateString(6, status);
            rsVac.updateString(7, date);
            rsVac.updateString(8, priority);

            rsVac.insertRow();
            loadDataVac();
            return true;
        } catch (Exception e) {
            System.out.println("Error in insertVac: " + e.getMessage());
            return false;
        }
    }

    public boolean updatateVac(Vector vVacData) {
        String id = vVacData.get(0).toString();
        int depNo = getDepNo(vVacData);
        String desId = getDesId(vVacData);
        String secId = getSecId(vVacData);
        int NoOf = Integer.parseInt(vVacData.get(4).toString());
        String status = getStatus(vVacData);
        String date = vVacData.get(6).toString();
        String priority = vVacData.get(7).toString();

        try {
            rsVac.beforeFirst();
            while (rsVac.next()) {
                if (rsVac.getString("VACANCYID").equals(id)) {

                    rsVac.updateString(1, id);
                    rsVac.updateInt(2, depNo);
                    rsVac.updateString(3, secId);
                    rsVac.updateString(4, desId);
                    rsVac.updateInt(5, NoOf);
                    rsVac.updateString(6, status);
                    rsVac.updateString(7, date);
                    rsVac.updateString(8, priority);

                    rsVac.updateRow();
                    loadDataVac();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Error in updatateVac: " + e.getMessage());
            return false;
        }
    }

    public boolean deleteVac(String VacId) {
        try {
            rsVac.beforeFirst();
            while (rsVac.next()) {
                if (rsVac.getString("VACANCYID").trim().equals(VacId)) {
                    rsVac.deleteRow();
                    loadDataVac();
                    return true;
                }
            }
            return false;
        } catch (Exception e) {
            System.out.println("Have data in orther table");
            return false;
        }
    }

    public void loadVacCombobox(JComboBox cbDep, JComboBox cbDes, JComboBox cbSec, JComboBox cbStatus) {
        vacLoadDepNo();
        vacLoadDesId();
        vacLoadSecId();
        vacLoadStatus();

        cbDep.removeAllItems();
        cbDes.removeAllItems();
        cbSec.removeAllItems();
        cbStatus.removeAllItems();

        cbDep.addItem("Please select...");
        cbDes.addItem("Please select...");
        cbSec.addItem("Please select...");
        cbStatus.addItem("Please select...");

        Set<Integer> keyDep = tmDep.keySet();
        for (int depNo : keyDep) {
            cbDep.addItem(depNo + " - " + tmDep.get(depNo));
        }

        Set<String> keyDes = tmDes.keySet();
        for (String desId : keyDes) {
            cbDes.addItem(desId + " - " + tmDes.get(desId));
        }

        Set<String> keySec = tmSec.keySet();
        for (String secId : keySec) {
            cbSec.addItem(secId + " - " + tmSec.get(secId));
        }

        Set<String> keyStatus = tmStatus.keySet();
        for (String status : keyStatus) {
            cbStatus.addItem(status + " - " + tmStatus.get(status));
        }
    }

    public DefaultTableModel loadTableVac() {
        try {
            String query = "Select * From Vacancies";
            Statement ps = conn.createStatement();
            ResultSet rs = ps.executeQuery(query);

            Vector nameCol = new Vector();
            nameCol.add("Vacancy ID");
            nameCol.add("Department No");
            nameCol.add("Section ID");
            nameCol.add("Designation ID");


            DefaultTableModel dtm = new DefaultTableModel(nameCol, 0);

            while (rs.next()) {

                Vector dataRow = new Vector();
                dataRow.add(rs.getString("VACANCYID"));
                dataRow.add(rs.getString("DEPTNO"));
                dataRow.add(rs.getString("SECTID"));
                dataRow.add(rs.getString("DESIGNID"));
                dtm.addRow(dataRow);
            }
            return dtm;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public void vacLoadDepNo() {
        try {
            Vector<Integer> vDepNo = new Vector<Integer>();
            Vector<String> vDepName = new Vector<String>();
            String query = "SELECT * FROM Departments";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                vDepNo.add(rs.getInt("DEPTNO"));
                vDepName.add(rs.getString("DEPTNAME").trim());
            }

            tmDep = new TreeMap<Integer, String>();
            for (int i = 0; i < vDepNo.size(); i++) {
                tmDep.put(vDepNo.get(i), vDepName.get(i));
            }

            rs.close();
            st.close();
        } catch (Exception e) {
            System.out.println("Error in vacLoadDepNo: " + e.getMessage());
        }
    }

    public void vacLoadDesId() {
        try {
            Vector<String> vDesId = new Vector<String>();
            Vector<String> vDesName = new Vector<String>();
            String query = "SELECT * FROM Designation";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                vDesId.add(rs.getString("DESIGNID").trim());
                vDesName.add(rs.getString("DESIGNATION").trim());
            }

            tmDes = new TreeMap<String, String>();
            for (int i = 0; i < vDesId.size(); i++) {
                tmDes.put(vDesId.get(i), vDesName.get(i));
            }

            rs.close();
            st.close();
        } catch (Exception e) {
            System.out.println("Error in vacLoadDesId: " + e.getMessage());
        }
    }

    public void vacLoadSecId() {
        try {
            Vector<String> vSecId = new Vector<String>();
            Vector<String> vSecName = new Vector<String>();
            String query = "SELECT * FROM Sections";
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery(query);
            while (rs.next()) {
                vSecId.add(rs.getString("SECTID").trim());
                vSecName.add(rs.getString("SECTNAME").trim());
            }

            tmSec = new TreeMap<String, String>();
            for (int i = 0; i < vSecId.size(); i++) {
                tmSec.put(vSecId.get(i), vSecName.get(i));
            }

            rs.close();
            st.close();
        } catch (Exception e) {
            System.out.println("Error in vacLoadSecId: " + e.getMessage());
        }
    }

    public void vacLoadStatus() {
        tmStatus = new TreeMap<String, String>();
        tmStatus.put("1", "Not Enough");
        tmStatus.put("0", "Enough");
    }

    public int getDepNo(Vector vVacData) {
        String depNo = (vVacData.get(1).toString().split(" "))[0];
        return Integer.parseInt(depNo);
    }

    public String getDesId(Vector vVacData) {
        String desId = (vVacData.get(3).toString().split(" "))[0];
        return desId;
    }

    public String getSecId(Vector vVacData) {
        String SecId = (vVacData.get(2).toString().split(" "))[0];
        return SecId;
    }

    public String getStatus(Vector vVacData) {
        String SecId = (vVacData.get(5).toString().split(" "))[0];
        return SecId;
    }

    public void closeConnect() {
        try {
            if (rsVac != null) {
                rsVac.close();
            }

            if (pst != null) {
                pst.close();
            }

            if (st != null) {
                st.close();
            }

            if (conn != null) {
                conn.close();
            }

            if (cdb.conn != null) {
                cdb.conn.close();
            }
            System.out.println("Close connect: Vacancy");
        } catch (Exception e) {
            System.out.println("Error in close connection: " + e.getMessage());
        }
    }
}
